﻿using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using Magicodes.ExporterAndImporter.Core;
using Magicodes.ExporterAndImporter.Core.Extension;
using Magicodes.ExporterAndImporter.Core.Models;
using Magicodes.ExporterAndImporter.Excel;
using NP.BPMReportPlatform.AUTO;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business;
using NP.BPMReportPlatform.Entities.BPM_Engine;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.SeminarManage.Dtos;
using NP.BPMReportPlatform.Utils;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using RazorEngine.Compilation.ImpromptuInterface;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;

namespace NP.BPMReportPlatform.SeminarManage
{
    public class SeminarManageService : BPMReportPlatformAppService, ISeminarManageService
    {
        private readonly SqlSugarClient _db, _dbEngine;
        private readonly SqlSugarClient _dbPlatform;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;

        public SeminarManageService(ISqlSugarAppService sqlSugarAppService
            , IBPMUtilsAppService bPMUtilsApp
            , IBPMPlatformAppService iBPMPlatformApp)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _iBPMPlatformApp = iBPMPlatformApp;
        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<SeminarManageOutput>> GetAllAsync(SeminarManageInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
            listViewDto.ForEach(x =>
            {
                x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId);
                var cost = _db.Queryable<TUSeminarManageCostDetails>().Where(y => y.InstanceNumber.Equals(x.InstanceNumber)).ToList();
                x.SiteCost = cost.Count > 0 ? cost.Where(x => x.CostKey.Contains("场地费")).FirstOrDefault().CostValue.ToString() : "0";
                x.AdCost = cost.Count > 0 ? cost.Where(x => x.CostKey.Contains("广宣费")).FirstOrDefault().CostValue.ToString() : "0";
                //获取住宿人数
                decimal totalZZSRS = cost.Count > 0 ? cost.Where(x => x.CostKey.Contains("住宿人数")).FirstOrDefault().CostValue : 0;
                //获取人天的住宿费用
                decimal totalZSFY = cost.Count > 0 ? cost.Where(x => x.CostKey.Contains("住宿费")).FirstOrDefault().CostValue : 0;
                x.Accommodation = totalZZSRS * totalZSFY;
                //获取实际人数
                decimal totalSJRS = cost.Count > 0 ? cost.Where(x => x.CostKey.Contains("实际人数")).FirstOrDefault().CostValue : 0;
                //获取接待费(元/人)
                decimal totalJDF = cost.Count > 0 ? cost.Where(x => x.CostKey.Contains("接待费")).FirstOrDefault().CostValue : 0;
                x.Entertainment = totalSJRS * totalJDF;//接待费（元/场）
                x.TotalCost = cost.Count > 0 ? cost.Where(x => x.CostKey.Contains("总费用")).FirstOrDefault().CostValue.ToString() : "0";
            });
            //返回结果
            return new PagedResultDto<SeminarManageOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        /// <summary>
        /// 查找区域生态伙伴年度合作共识会
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<AreaSeminarManageOutput>> GetAreaAllAsync(SeminarManageInput input)
        {
            try
            {
                RefAsync<int> totalCount = 0;
                var listQueryable = GetAreaQueryable(input);
                var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);
                var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
                listViewDto.ForEach(x =>
                {
                    x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId);
                });
                //返回结果
                return new PagedResultDto<AreaSeminarManageOutput>()
                {
                    TotalCount = totalCount,
                    Items = listViewDto
                };
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 导出列表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<MemoryStream> Export1Async(SeminarManageInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryable(input);
            var listViewDto = listQueryable.ToList();

            var fs = File.OpenRead(Environment.CurrentDirectory + "/wwwroot/excel/推广会导出模板.xlsx");
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);

            // 全局样式
            ICellStyle borderStyle = workbook.CreateCellStyle();
            borderStyle.BorderLeft = BorderStyle.Thin;
            borderStyle.BorderRight = BorderStyle.Thin;
            borderStyle.BorderTop = BorderStyle.Thin;
            borderStyle.BorderBottom = BorderStyle.Thin;
            for (int i = 0; i < listViewDto.Count; i++)
            {
                List<string> columns = new List<string>();
                var cost = _db.Queryable<TUSeminarManageCostDetails>().Where(y => y.InstanceNumber.Equals(listViewDto[i].InstanceNumber)).ToList();
                listViewDto[i].SiteCost = cost.Where(x => x.CostKey.Contains("场地费")).FirstOrDefault() == null ? "0" : cost.Where(x => x.CostKey.Contains("场地费")).FirstOrDefault().CostValue.ToString();
                listViewDto[i].AdCost = cost.Where(x => x.CostKey.Contains("广宣费")).FirstOrDefault() == null ? "0" : cost.Where(x => x.CostKey.Contains("广宣费")).FirstOrDefault().CostValue.ToString();
                //获取住宿人数
                decimal totalZZSRS = cost.Where(x => x.CostKey.Contains("住宿人数")).FirstOrDefault() == null ? 0 : cost.Where(x => x.CostKey.Contains("住宿人数")).FirstOrDefault().CostValue;
                //获取人天的住宿费用
                decimal totalZSFY = cost.Where(x => x.CostKey.Contains("住宿费")).FirstOrDefault() == null ? 0 : cost.Where(x => x.CostKey.Contains("住宿费")).FirstOrDefault().CostValue;
                listViewDto[i].Accommodation = totalZZSRS * totalZSFY;
                //获取实际人数
                decimal totalSJRS = cost.Where(x => x.CostKey.Contains("实际人数")).FirstOrDefault() == null ? 0 : cost.Where(x => x.CostKey.Contains("实际人数")).FirstOrDefault().CostValue;
                //获取接待费(元/人)
                decimal totalJDF = cost.Where(x => x.CostKey.Contains("接待费")).FirstOrDefault() == null ? 0 : cost.Where(x => x.CostKey.Contains("接待费")).FirstOrDefault().CostValue;
                listViewDto[i].Entertainment = totalSJRS * totalJDF;//接待费（元/场）
                listViewDto[i].TotalCost = cost.Where(x => x.CostKey.Contains("总费用")).FirstOrDefault() == null ? "0" : cost.Where(x => x.CostKey.Contains("总费用")).FirstOrDefault().CostValue.ToString();

                columns.Add(listViewDto[i].UserName);
                columns.Add(listViewDto[i].CreateTime);
                columns.Add(listViewDto[i].InstanceNumber);
                columns.Add(listViewDto[i].OrderStatus);
                columns.Add(listViewDto[i].AreaCompany);
                columns.Add(listViewDto[i].Applicant);
                columns.Add(listViewDto[i].SaleCompany);
                columns.Add(listViewDto[i].CostCenter);
                columns.Add(listViewDto[i].SaleDepartment);
                columns.Add(listViewDto[i].MeetTime);
                columns.Add(listViewDto[i].MeetPlace);
                columns.Add(listViewDto[i].MeetNumber == null ? "" : listViewDto[i].MeetNumber.ToString());
                columns.Add(listViewDto[i].MeetAim);
                columns.Add(listViewDto[i].IsNeedRentMeet);
                columns.Add(listViewDto[i].MeetBudget == null ? "" : listViewDto[i].MeetBudget.ToString());
                columns.Add(listViewDto[i].MeetType);
                columns.Add(listViewDto[i].ProductLineType);
                columns.Add(listViewDto[i].PushProductSolution);
                columns.Add(listViewDto[i].MeetDemand);
                columns.Add(listViewDto[i].MeetDemandOther);
                columns.Add(listViewDto[i].Director);
                columns.Add(listViewDto[i].Lecturer);
                columns.Add(listViewDto[i].NewCustomer);
                columns.Add(listViewDto[i].CustomerGroup);
                columns.Add(listViewDto[i].CustomerCode);
                columns.Add(listViewDto[i].CustomerName);
                columns.Add(listViewDto[i].CustomerLevel);
                columns.Add(listViewDto[i].DesignCode);
                columns.Add(listViewDto[i].DesignName);
                columns.Add(listViewDto[i].BusinessNumber);
                columns.Add(listViewDto[i].EntryName);
                columns.Add(listViewDto[i].ProjectProperties);
                columns.Add(listViewDto[i].BigCustomerType);
                columns.Add(listViewDto[i].BigCustomerCode);
                columns.Add(listViewDto[i].BigCustomerName);
                columns.Add(listViewDto[i].Projectlocation);
                columns.Add(listViewDto[i].ProjectCity);
                columns.Add(listViewDto[i].ProjectStatus);
                columns.Add(listViewDto[i].BusinessSource);
                columns.Add(listViewDto[i].CustomerType1);
                columns.Add(listViewDto[i].CustomerType2);
                columns.Add(listViewDto[i].SingnTime);
                columns.Add(listViewDto[i].ProjectAssurance);
                columns.Add(listViewDto[i].CoatingArea);
                columns.Add(listViewDto[i].ShipmentAmount == null ? "" : listViewDto[i].ShipmentAmount.ToString());
                columns.Add(listViewDto[i].DesignDept);
                columns.Add(listViewDto[i].ProvinceName);
                columns.Add(listViewDto[i].City);
                columns.Add(listViewDto[i].ActiveTime);
                columns.Add(listViewDto[i].ExecutionSite);
                columns.Add(listViewDto[i].OverallExpenses == null ? "" : listViewDto[i].OverallExpenses.ToString());
                columns.Add(listViewDto[i].PlanJoinCustomer == null ? "" : listViewDto[i].PlanJoinCustomer.ToString());
                columns.Add(listViewDto[i].PlanJoinPerson == null ? "" : listViewDto[i].PlanJoinPerson.ToString());
                columns.Add(listViewDto[i].RealityJoinCustomer == null ? "" : listViewDto[i].RealityJoinCustomer.ToString());
                columns.Add(listViewDto[i].RealityJoinPerson == null ? "" : listViewDto[i].RealityJoinPerson.ToString());
                columns.Add(listViewDto[i].SiteCost);
                columns.Add(listViewDto[i].AdCost);
                columns.Add(listViewDto[i].Accommodation.ToString());
                columns.Add(listViewDto[i].Entertainment.ToString());
                columns.Add(listViewDto[i].TotalCost);
                columns.Add(listViewDto[i].ActivityEvaluation);
                columns.Add(listViewDto[i].Feedback);

                IRow row = sheet.CreateRow(i + 2);
                for (int j = 0; j < columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(columns[j]);
                    cell.CellStyle = borderStyle;
                }
            }

            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<ExportSeminarManageOutput> GetQueryableExport(SeminarManageInput input)
        {
            var currentUserId = Guid.Empty;

            var listQueryable = _db.Queryable<TUSeminarManageHeader, BusinessProcessesNew, TUSeminarManageCaseCustomerDetails>(
                    (x, bu, d1) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId,
                    JoinType.Left, x.InstanceNumber == d1.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), (x, bu, d1) => x.InstanceNumber.Contains(input.InstanceNumber))
                //.WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)

                .Select((x, bu, d1) =>
                    new ExportSeminarManageOutput()
                    {
                        InstanceNumber = x.InstanceNumber,
                    }).MergeTable().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);

            return listQueryable;
        }

        /// <summary>
        /// 区域生态伙伴年度合作共识会导出
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<AreaSeminarManageOutput>> AreaExport(SeminarManageInput input)
        {
            var listQueryable = GetAreaQueryable(input);
            var listViewDto = await listQueryable.ToListAsync();
            foreach (var item in listViewDto)
            {
                var result = await _db.Queryable<TUSeminarManageCostDetails>().Where(x => x.InstanceNumber == item.InstanceNumber).ToListAsync();
                if (result.Count > 0)
                {
                    item.PerCapitaCatering = result.Where(x => x.CostKey == "实际嘉宾人均餐饮费（元/人）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际嘉宾人均餐饮费（元/人）").FirstOrDefault().CostValue;
                    item.Catering = result.Where(x => x.CostKey == "实际嘉宾餐饮费（元）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际嘉宾餐饮费（元）").FirstOrDefault().CostValue;
                    item.NumOfStay = result.Where(x => x.CostKey == "实际嘉宾住宿人数").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际嘉宾住宿人数").FirstOrDefault().CostValue;
                    item.PerCapitaStay = result.Where(x => x.CostKey == "实际嘉宾人均住宿费（元/人）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际嘉宾人均住宿费（元/人）").FirstOrDefault().CostValue;
                    item.Stay = result.Where(x => x.CostKey == "实际嘉宾住宿费（元）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际嘉宾住宿费（元）").FirstOrDefault().CostValue;
                    item.VenueCooperation = result.Where(x => x.CostKey == "实际场地合作费（元）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际场地合作费（元）").FirstOrDefault().CostValue;
                    item.ActiveDesignBuild = result.Where(x => x.CostKey == "实际活动设计搭建费（元）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际活动设计搭建费（元）").FirstOrDefault().CostValue;
                    item.GuestTransportation = result.Where(x => x.CostKey == "实际嘉宾交通费（元）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际嘉宾交通费（元）").FirstOrDefault().CostValue;
                    item.GiftOrder = result.Where(x => x.CostKey == "实际礼品订购费（元）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际礼品订购费（元）").FirstOrDefault().CostValue;
                    item.SceneSupport = result.Where(x => x.CostKey == "实际现场支持费（元）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际现场支持费（元）").FirstOrDefault().CostValue;
                    item.TotalCost = result.Where(x => x.CostKey == "实际会议总费用（元）").FirstOrDefault() == null ? 0 : result.Where(x => x.CostKey == "实际会议总费用（元）").FirstOrDefault().CostValue;
                }
            }
            return listViewDto;
        }

        /// <summary>
        /// 查询 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<SeminarManageOutput> GetQueryable(SeminarManageInput input)
        {
            var listQueryable = _db.Queryable<TUSeminarManageHeader, BusinessProcessesNew, TUSeminarManageCaseCustomerDetails, TUSeminarManageCustomerDetails>(
                (x, bu, m, customer) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId, JoinType.Left, x.InstanceNumber == m.InstanceNumber,
                 JoinType.Left, x.InstanceNumber == customer.InstanceNumber))
            .WhereIF(input.MeetType == "361专项推广会", (x, bu, m, customer) => customer.CustomerCode != null)
            .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber.Contains(input.InstanceNumber))
            .WhereIF(!string.IsNullOrWhiteSpace(input.AddName), x => x.AddUserName.Contains(input.AddName))
            .WhereIF(!string.IsNullOrWhiteSpace(input.Applicant), x => x.Applicant.Contains(input.Applicant))
            .WhereIF(!string.IsNullOrWhiteSpace(input.AreaCompany), x => x.AreaCompany.Equals(input.AreaCompany))
            .WhereIF(!string.IsNullOrWhiteSpace(input.MeetType), x => x.MeetType.Equals(input.MeetType))
            .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
            .Where(x => x.MeetType != "区域生态伙伴年度合作共识会" && !x.MeetType.Equals("项目推广会") && !x.MeetType.Equals("合作伙伴推广会") && !x.MeetType.Equals("客户推广会")
            && !x.MeetType.Equals("设计院推广会") && !x.MeetType.Equals("行业交流"))
            .Select((x, bu, m, customer) => new SeminarManageOutput()
            {
                InstanceNumber = x.InstanceNumber,
                AreaCompany = x.AreaCompany,
                SaleCompany = x.SaleCompany,
                ProductLineType = x.ProductLineType,//产品线类型
                Lecturer = x.Lecturer,//讲师
                MeetAim = x.MeetAim,//会议目的
                PushProductSolution = x.PushProductSolution,//解决方案
                MeetDemand = x.MeetDemand,//会议需求
                MeetDemandOther = x.MeetDemandOther,//其它
                NewCustomer = x.NewCustomer,//是否新用户
                CustomerLevel = x.CustomerLevel,//客户等级
                Director = x.Director,
                AddUserName = x.AddUserName,
                ProvinceName = x.ProvinceName,
                City = x.City,
                ExecutionSite = x.ExecutionSite,
                OverallExpenses = x.OverallExpenses,
                OrderStatus = string.IsNullOrEmpty(bu.ProcessStatusName) ? "未知" : bu.ProcessStatusName,
                CreateTime = x.AddTime.ToString("yyyy-MM-dd HH:mm:ss"),
                UserName = x.AddUserName,
                Applicant = x.Applicant,
                CostCenter = x.CostCenter,
                SaleDepartment = x.SaleDepartment,
                MeetTime = x.MeetTime != null ? x.MeetTime.Value.ToString("yyyy-MM-dd") : "",
                MeetPlace = x.MeetPlace,
                MeetNumber = x.MeetNumber,
                IsNeedRentMeet = x.IsNeedRentMeet,
                MeetBudget = x.MeetBudget,
                MeetType = x.MeetType,
                CustomerGroup = x.CustomerGroup,
                CustomerCode = x.MeetType == "N+力量落地" ? m.CustomerCode : x.MeetType == "361专项推广会" ? customer.CustomerCode : x.CustomerCode,
                CustomerName = x.MeetType == "N+力量落地" ? m.CustomerName : x.MeetType == "361专项推广会" ? customer.CustomerName : x.CustomerName,
                DesignCode = x.DesignCode,
                DesignName = x.DesignName,
                DesignDept = x.DesignDept,
                BusinessNumber = x.BusinessNumber,
                EntryName = x.EntryName,
                ProjectProperties = x.ProjectProperties,
                BigCustomerType = x.BigCustomerType,
                BigCustomerCode = x.BigCustomerCode,
                BigCustomerName = x.BigCustomerName,
                Projectlocation = x.Projectlocation,
                ProjectCity = x.ProjectCity,
                ProjectStatus = x.ProjectStatus,
                BusinessSource = x.BusinessSource,
                CustomerType1 = x.CustomerType1,
                CustomerType2 = x.CustomerType2,
                SingnTime = x.SingnTime != null ? x.SingnTime.Value.ToString("yyyy-MM-dd") : "",
                ProjectAssurance = x.ProjectAssurance,
                CoatingArea = x.CoatingArea,
                ShipmentAmount = x.ShipmentAmount,
                ActiveTime = x.ActiveTime != null ? x.ActiveTime.Value.ToString("yyyy-MM-dd") : "",
                PlanJoinCustomer = x.PlanJoinCustomer,
                PlanJoinPerson = x.MeetType == "N+力量落地" || x.MeetType == "361专项推广会" ? m.PlanJoinNum : x.PlanJoinPerson,
                RealityJoinCustomer = x.RealityJoinCustomer,
                RealityJoinPerson = x.MeetType == "N+力量落地" || x.MeetType == "361专项推广会" ? m.RealityJoinNum : x.RealityJoinPerson,
                ActivityEvaluation = x.ActivityEvaluation,
                Feedback = x.Feedback
            });

            var listDesign = _db.Queryable<TUSeminarManageHeader, BusinessProcessesNew, TUSeminarManageDesignDetails>(
                    (x, bu, design) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId, JoinType.Left, x.InstanceNumber == design.InstanceNumber))
                .Where((x, bu, design) => design.DesignCode != null)
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddName), x => x.AddUserName.Contains(input.AddName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.Applicant), x => x.Applicant.Contains(input.Applicant))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AreaCompany), x => x.AreaCompany.Equals(input.AreaCompany))
                .WhereIF(!string.IsNullOrWhiteSpace(input.MeetType), x => x.MeetType.Equals(input.MeetType))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                .Where(x => x.MeetType != "区域生态伙伴年度合作共识会" && !x.MeetType.Equals("项目推广会") && !x.MeetType.Equals("合作伙伴推广会") && !x.MeetType.Equals("客户推广会")
                && !x.MeetType.Equals("设计院推广会") && !x.MeetType.Equals("行业交流"))
                .Select((x, bu, design) => new SeminarManageOutput()
                {
                    InstanceNumber = x.InstanceNumber,
                    AreaCompany = x.AreaCompany,
                    SaleCompany = x.SaleCompany,
                    ProductLineType = x.ProductLineType,//产品线类型
                    Lecturer = x.Lecturer,//讲师
                    MeetAim = x.MeetAim,//会议目的
                    PushProductSolution = x.PushProductSolution,//解决方案
                    MeetDemand = x.MeetDemand,//会议需求
                    MeetDemandOther = x.MeetDemandOther,//其它
                    NewCustomer = x.NewCustomer,//是否新用户
                    CustomerLevel = x.CustomerLevel,//客户等级
                    Director = x.Director,
                    AddUserName = x.AddUserName,
                    ProvinceName = x.ProvinceName,
                    City = x.City,
                    ExecutionSite = x.ExecutionSite,
                    OverallExpenses = x.OverallExpenses,
                    OrderStatus = string.IsNullOrEmpty(bu.ProcessStatusName) ? "未知" : bu.ProcessStatusName,
                    CreateTime = x.AddTime.ToString("yyyy-MM-dd HH:mm:ss"),
                    UserName = x.AddUserName,
                    Applicant = x.Applicant,
                    CostCenter = x.CostCenter,
                    SaleDepartment = x.SaleDepartment,
                    MeetTime = x.MeetTime != null ? x.MeetTime.Value.ToString("yyyy-MM-dd") : "",
                    MeetPlace = x.MeetPlace,
                    MeetNumber = x.MeetNumber,
                    IsNeedRentMeet = x.IsNeedRentMeet,
                    MeetBudget = x.MeetBudget,
                    MeetType = x.MeetType,
                    CustomerGroup = x.CustomerGroup,
                    CustomerCode = "",
                    CustomerName = "",
                    DesignCode = design.DesignCode,
                    DesignName = design.DesignName,
                    DesignDept = design.DesignDept,
                    BusinessNumber = x.BusinessNumber,
                    EntryName = x.EntryName,
                    ProjectProperties = x.ProjectProperties,
                    BigCustomerType = x.BigCustomerType,
                    BigCustomerCode = x.BigCustomerCode,
                    BigCustomerName = x.BigCustomerName,
                    Projectlocation = x.Projectlocation,
                    ProjectCity = x.ProjectCity,
                    ProjectStatus = x.ProjectStatus,
                    BusinessSource = x.BusinessSource,
                    CustomerType1 = x.CustomerType1,
                    CustomerType2 = x.CustomerType2,
                    SingnTime = x.SingnTime != null ? x.SingnTime.Value.ToString("yyyy-MM-dd") : "",
                    ProjectAssurance = x.ProjectAssurance,
                    CoatingArea = x.CoatingArea,
                    ShipmentAmount = x.ShipmentAmount,
                    ActiveTime = x.ActiveTime != null ? x.ActiveTime.Value.ToString("yyyy-MM-dd") : "",
                    PlanJoinCustomer = x.PlanJoinCustomer,
                    PlanJoinPerson = x.PlanJoinPerson,
                    RealityJoinCustomer = x.RealityJoinCustomer,
                    RealityJoinPerson = x.RealityJoinPerson,
                    ActivityEvaluation = x.ActivityEvaluation,
                    Feedback = x.Feedback
                });

            var result = _db.Union(listQueryable, listDesign).MergeTable().OrderBy(ins => ins.InstanceNumber, OrderByType.Desc);
            return result;
        }

        /// <summary>
        /// 区域生态伙伴年度合作共识会查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<AreaSeminarManageOutput> GetAreaQueryable(SeminarManageInput input)
        {
            var listQueryable = _db.Queryable<TUSeminarManageHeader, BusinessProcessesNew>
                    ((x, bu) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber.Contains(input.InstanceNumber))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddName), x => x.AddUserName.Contains(input.AddName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.Applicant), x => x.Applicant.Contains(input.Applicant))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AreaCompany), x => x.AreaCompany.Equals(input.AreaCompany))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
                .Where(x => x.MeetType == "区域生态伙伴年度合作共识会")
                .Select((x, bu) => new AreaSeminarManageOutput()
                {
                    CreateTime = x.AddTime.ToString("yyyy-MM-dd HH:mm:ss"),
                    InstanceNumber = x.InstanceNumber,
                    OrderStatus = string.IsNullOrEmpty(bu.ProcessStatusName) ? "未知" : bu.ProcessStatusName,
                    AddUser = x.AddUserName,
                    UserName = x.Applicant,
                    AreaCompany = x.AreaCompany,
                    MeetType = x.MeetType,
                    CostCenter = x.CostCenter,
                    MeetTime = x.MeetTime.Value.ToString("yyyy-MM-dd HH:mm:ss"),
                    MeetPlace = x.MeetPlace,
                    IsNeedRentMeet = x.IsNeedRentMeet,
                    EntryName = x.EntryName,
                    MeetAim = x.MeetAim,
                    MeetNumber = x.MeetNumber,
                    PushProductSolution = x.PushProductSolution,
                    CaseDate = x.CaseDate.Value.ToString("yyyy-MM-dd HH:mm:ss"),
                    ActiveTime = x.ActiveTime.Value.ToString("yyyy-MM-dd HH:mm:ss"),
                    ProvinceName = x.ProvinceName,
                    City = x.City,
                    CityLevel = x.CityLevel,
                    ExecutionSite = x.ExecutionSite,
                    FieldLevel = x.FieldLevel,
                    RealityJoinCustomer = x.RealityJoinCustomer,
                    RealityJoinPerson = x.RealityJoinPerson,
                    OverallExpenses = x.OverallExpenses == null ? 0 : x.OverallExpenses.Value,
                });
            return listQueryable;
        }

        /// <summary>
        /// 查询数据2024年新推广会
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<SeminarManageFor2024Output>> GetAllAsyncFor2024(SeminarManageInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetQueryableFor2024(input);
            var isDeleted = await _db.Queryable<ApproverConfiguration>().Where(x => x.BTID.Equals("SeminarManage") && x.ApprovalNode.Equals("QYDJR") && x.EmployeeCode.Equals(CurrentUser.UserName)).ToListAsync();
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);
            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);
            listViewDto.ForEach(e =>
            {
                e.Url = _bPMUtilsApp.GetProcessUrl(e.InstanceNumber, userId);
                e.IsDelete = isDeleted.Count > 0 ? true : false;
            });
            //返回结果
            return new PagedResultDto<SeminarManageFor2024Output>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        /// <summary>
        /// 查找区域生态伙伴年度合作共识会2024年新推广会
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<MemoryStream> ExportFor2024Async(SeminarManageInput input)
        {
            RefAsync<int> totalCount = 0;
            var listQueryable = GetExport1For2024(input);
            var listViewDto = listQueryable.ToList();

            var fs = File.OpenRead(Environment.CurrentDirectory + "/wwwroot/excel/一般推广会导出模板.xlsx");
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);

            // 全局样式
            ICellStyle borderStyle = workbook.CreateCellStyle();
            borderStyle.BorderLeft = BorderStyle.Thin;
            borderStyle.BorderRight = BorderStyle.Thin;
            borderStyle.BorderTop = BorderStyle.Thin;
            borderStyle.BorderBottom = BorderStyle.Thin;
            for (int i = 0; i < listViewDto.Count; i++)
            {
                List<string> columns = new List<string>();
                var cost = _db.Queryable<TUSeminarManageLecturerSurvey>().Where(y => y.InstanceNumber.Equals(listViewDto[i].InstanceNumber)).ToList();

                listViewDto[i].Score0 = cost.Where(x => x.EvaluationCriteria == "报告和推广会主题切合度（0-2分）").FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationCriteria == "报告和推广会主题切合度（0-2分）").FirstOrDefault().Score;
                listViewDto[i].Score1 = cost.Where(x => x.EvaluationCriteria == "讲解的完整性和专业性（0-3分）").FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationCriteria == "讲解的完整性和专业性（0-3分）").FirstOrDefault().Score;
                listViewDto[i].Score2 = cost.Where(x => x.EvaluationCriteria == "客户对解决方案的认可度（0-2分）").FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationCriteria == "客户对解决方案的认可度（0-2分）").FirstOrDefault().Score;
                listViewDto[i].Score3 = cost.Where(x => x.EvaluationCriteria == "和客户互动及解答满意度（0-1分）").FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationCriteria == "和客户互动及解答满意度（0-1分）").FirstOrDefault().Score;
                listViewDto[i].Score4 = cost.Where(x => x.EvaluationCriteria == "语言表达清晰流畅（0-2分）").FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationCriteria == "语言表达清晰流畅（0-2分）").FirstOrDefault().Score;
                listViewDto[i].Score5 = cost.Where(x => x.EvaluationProject == "综合得分" && x.Type.Equals("LecturerSatisfaction")).FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationProject == "综合得分" && x.Type.Equals("LecturerSatisfaction")).FirstOrDefault().Score;

                listViewDto[i].Score6 = cost.Where(x => x.EvaluationCriteria == "活动的组织能力（0-3分）").FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationCriteria == "活动的组织能力（0-3分）").FirstOrDefault().Score;
                listViewDto[i].Score7 = cost.Where(x => x.EvaluationCriteria == "客户认可度（0-5分）").FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationCriteria == "客户认可度（0-5分）").FirstOrDefault().Score;
                listViewDto[i].Score8 = cost.Where(x => x.EvaluationCriteria == "解决方案的合理性（0-2分）").FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationCriteria == "解决方案的合理性（0-2分）").FirstOrDefault().Score;
                listViewDto[i].Score9 = cost.Where(x => x.EvaluationCriteria == "综合得分" && x.Type.Equals("SeminarManageSatisfaction")).FirstOrDefault() == null ? "0" : cost.Where(x => x.EvaluationCriteria == "综合得分" && x.Type.Equals("SeminarManageSatisfaction")).FirstOrDefault().Score;

                columns.Add(listViewDto[i].UserName);
                columns.Add(listViewDto[i].CreateTime);
                columns.Add(listViewDto[i].InstanceNumber);
                columns.Add(listViewDto[i].OrderStatus);
                columns.Add(listViewDto[i].AreaCompany);
                columns.Add(listViewDto[i].Applicant);
                columns.Add(listViewDto[i].SaleCompany);
                columns.Add(listViewDto[i].CostCenter);
                columns.Add(listViewDto[i].SaleDepartment);
                columns.Add(listViewDto[i].Division);
                columns.Add(listViewDto[i].MeetTime);
                columns.Add(listViewDto[i].MeetPlace);
                columns.Add(listViewDto[i].MeetNumber == null ? "" : listViewDto[i].MeetNumber.ToString());
                columns.Add(listViewDto[i].MeetAim);
                columns.Add(listViewDto[i].IsNeedRentMeet);
                columns.Add(listViewDto[i].PushProductSolution);
                columns.Add(listViewDto[i].MeetBudget == null ? "" : listViewDto[i].MeetBudget.ToString());
                columns.Add(listViewDto[i].ExpectExtras.ToString());
                columns.Add(listViewDto[i].MeetType);
                columns.Add(listViewDto[i].MeetModality);
                columns.Add(listViewDto[i].MeetModalityOther);
                columns.Add(listViewDto[i].ProductLineType);
                columns.Add(listViewDto[i].MeetDemand);
                columns.Add(listViewDto[i].MeetDemandOther);
                columns.Add(listViewDto[i].Director);
                columns.Add(listViewDto[i].Lecturer);
                columns.Add(listViewDto[i].CustomerCode);
                columns.Add(listViewDto[i].CustomerName);
                columns.Add(listViewDto[i].DesignCode);
                columns.Add(listViewDto[i].DesignName);
                columns.Add(listViewDto[i].Guild);
                columns.Add(listViewDto[i].BusinessNumber);
                columns.Add(listViewDto[i].EntryName);
                columns.Add(listViewDto[i].ProjectProperties);
                columns.Add(listViewDto[i].BigCustomerCode);
                columns.Add(listViewDto[i].BigCustomerName);
                columns.Add(listViewDto[i].Projectlocation);
                columns.Add(listViewDto[i].ProjectCity);
                columns.Add(listViewDto[i].ProjectStatus);
                columns.Add(listViewDto[i].BusinessSource);
                columns.Add(listViewDto[i].CustomerType1);
                columns.Add(listViewDto[i].CustomerType2);
                columns.Add(listViewDto[i].SingnTime);
                columns.Add(listViewDto[i].ProjectAssurance);
                columns.Add(listViewDto[i].CoatingArea);
                columns.Add(listViewDto[i].ShipmentAmount == null ? "" : listViewDto[i].ShipmentAmount.ToString());
                columns.Add(listViewDto[i].CaseDate);
                columns.Add(listViewDto[i].ProvinceName);
                columns.Add(listViewDto[i].City);
                columns.Add(listViewDto[i].ActiveTime);
                columns.Add(listViewDto[i].ExecutionSite);
                columns.Add(listViewDto[i].MeetNumber.ToString());
                columns.Add(listViewDto[i].RealityJoinPerson.ToString());
                columns.Add(listViewDto[i].RealMealsBill.ToString());
                columns.Add(listViewDto[i].RealMiscellaneousBill.ToString());
                columns.Add(listViewDto[i].OverallExpenses.ToString());
                columns.Add(listViewDto[i].PerCapitaCost.ToString());
                columns.Add(listViewDto[i].ActivityEvaluation);
                columns.Add(listViewDto[i].Score0);
                columns.Add(listViewDto[i].Score1);
                columns.Add(listViewDto[i].Score2);
                columns.Add(listViewDto[i].Score3);
                columns.Add(listViewDto[i].Score4);
                columns.Add(listViewDto[i].Score5);
                columns.Add(listViewDto[i].Score6);
                columns.Add(listViewDto[i].Score7);
                columns.Add(listViewDto[i].Score8);
                columns.Add(listViewDto[i].Score9);

                IRow row = sheet.CreateRow(i + 2);
                for (int j = 0; j < columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(columns[j]);
                    cell.CellStyle = borderStyle;
                }
            }

            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }

        /// <summary>
        /// 作废单号
        /// </summary>
        /// <returns></returns>
        public async Task<bool> Cancel(string number)
        {
            try
            {
                var result = await _db.Queryable<TUSeminarManageHeader>().Where(x => x.InstanceNumber.Equals(number)).FirstAsync();
                if (result == null)
                {
                    return false;
                }
                result.StatusText = "已作废";
                result.Status = 2;
                await _db.Updateable(result).ExecuteCommandAsync();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 24年一般数据库查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<SeminarManageFor2024Output> GetQueryableFor2024(SeminarManageInput input)
        {
            var listQueryable = _db.Queryable<TUSeminarManageHeader, BusinessProcessesNew>(
                (x, bu) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId))
            .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber.Contains(input.InstanceNumber))
            .WhereIF(!string.IsNullOrWhiteSpace(input.AddName), x => x.AddUserName.Contains(input.AddName))
            .WhereIF(!string.IsNullOrWhiteSpace(input.Applicant), x => x.Applicant.Contains(input.Applicant))
            .WhereIF(!string.IsNullOrWhiteSpace(input.AreaCompany), x => x.AreaCompany.Equals(input.AreaCompany))
            .WhereIF(!string.IsNullOrWhiteSpace(input.MeetType), x => x.MeetType.Equals(input.MeetType))
            .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
            .Where(x => (x.MeetType.Equals("项目推广会") || x.MeetType.Equals("合作伙伴推广会") || x.MeetType.Equals("客户推广会")
            || x.MeetType.Equals("设计院推广会") || x.MeetType.Equals("行业交流")) && x.AddTime >= DateTime.Parse("2024-06-25 0:00:00"))
            .Select((x, bu) => new SeminarManageFor2024Output()
            {
                InstanceNumber = x.InstanceNumber,
                AreaCompany = x.AreaCompany,
                SaleCompany = x.SaleCompany,
                ProductLineType = x.ProductLineType,//产品线类型
                Lecturer = x.Lecturer,//讲师
                MeetAim = x.MeetAim,//会议目的
                PushProductSolution = x.PushProductSolution,//解决方案
                MeetDemand = x.MeetDemand,//会议需求
                MeetModality = x.MeetModality,//会议形式
                MeetDemandOther = x.MeetDemandOther,//其它
                NewCustomer = x.NewCustomer,//是否新用户
                CustomerLevel = x.CustomerLevel,//客户等级
                Director = x.Director,
                AddUserName = x.AddUserName,
                ProvinceName = x.ProvinceName,
                City = x.City,
                ExecutionSite = x.ExecutionSite,
                OverallExpenses = x.OverallExpenses,
                OrderStatus = string.IsNullOrEmpty(bu.ProcessStatusName) ? "未知" : bu.ProcessStatusName,
                CreateTime = x.AddTime.ToString("yyyy-MM-dd HH:mm:ss"),
                UserName = x.AddUserName,
                Applicant = x.Applicant,
                CostCenter = x.CostCenter,
                SaleDepartment = x.SaleDepartment,
                MeetTime = x.MeetTime != null ? x.MeetTime.Value.ToString("yyyy-MM-dd") : "",
                MeetPlace = x.MeetPlace,
                MeetNumber = x.MeetNumber,
                IsNeedRentMeet = x.IsNeedRentMeet,
                MeetBudget = x.MeetBudget.Value,
                MeetType = x.MeetType,
                CustomerCode = x.CustomerCode,
                CustomerName = x.CustomerName,
                DesignCode = x.DesignCode,
                DesignName = x.DesignName,
                DesignDept = x.DesignDept,
                BusinessNumber = x.BusinessNumber,
                EntryName = x.EntryName,
                ProjectProperties = x.ProjectProperties,
                BigCustomerType = x.BigCustomerType,
                BigCustomerCode = x.BigCustomerCode,
                BigCustomerName = x.BigCustomerName,
                Projectlocation = x.Projectlocation,
                ProjectCity = x.ProjectCity,
                ProjectStatus = x.ProjectStatus,
                BusinessSource = x.BusinessSource,
                CustomerType1 = x.CustomerType1,
                CustomerType2 = x.CustomerType2,
                SingnTime = x.SingnTime != null ? x.SingnTime.Value.ToString("yyyy-MM-dd") : "",
                ProjectAssurance = x.ProjectAssurance,
                CoatingArea = x.CoatingArea,
                ShipmentAmount = x.ShipmentAmount,
                ActiveTime = x.ActiveTime != null ? x.ActiveTime.Value.ToString("yyyy-MM-dd") : "",
                PlanJoinCustomer = x.PlanJoinCustomer,
                RealityJoinCustomer = x.RealityJoinCustomer,
                RealityJoinPerson = x.RealityJoinPerson,
                ActivityEvaluation = x.ActivityEvaluation,
                Feedback = x.Feedback
            }).OrderBy(x => x.AddTime, OrderByType.Desc);

            return listQueryable;
        }

        private ISugarQueryable<SeminarManageFor2024Output> GetExport1For2024(SeminarManageInput input)
        {
            //var userId = _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName).Result;
            var listQueryable = _db.Queryable<TUSeminarManageHeader, BusinessProcessesNew>(
                (x, bu) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == bu.InstanceId))
            .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber.Contains(input.InstanceNumber))
            .WhereIF(!string.IsNullOrWhiteSpace(input.AddName), x => x.AddUserName.Contains(input.AddName))
            .WhereIF(!string.IsNullOrWhiteSpace(input.Applicant), x => x.Applicant.Contains(input.Applicant))
            .WhereIF(!string.IsNullOrWhiteSpace(input.AreaCompany), x => x.AreaCompany.Equals(input.AreaCompany))
            .WhereIF(!string.IsNullOrWhiteSpace(input.MeetType), x => x.MeetType.Equals(input.MeetType))
            .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime.AddDays(-1) <= input.EndCreationTime)
            .Where(x => (x.MeetType.Equals("项目推广会") || x.MeetType.Equals("合作伙伴推广会") || x.MeetType.Equals("客户推广会")
            || x.MeetType.Equals("设计院推广会") || x.MeetType.Equals("行业交流")) && x.AddTime >= DateTime.Parse("2024-06-25 0:00:00"))
            .Select((x, bu) => new SeminarManageFor2024Output()
            {
                //Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId),
                InstanceNumber = x.InstanceNumber,
                AreaCompany = x.AreaCompany,
                SaleCompany = x.SaleCompany,
                ProductLineType = x.ProductLineType,//产品线类型
                Lecturer = x.Lecturer,//讲师
                MeetAim = x.MeetAim,//会议目的
                PushProductSolution = x.PushProductSolution,//解决方案
                MeetDemand = x.MeetDemand,//会议需求
                MeetDemandOther = x.MeetDemandOther,//其它
                NewCustomer = x.NewCustomer,//是否新用户
                CustomerLevel = x.CustomerLevel,//客户等级
                Director = x.Director,
                AddUserName = x.AddUserName,
                ProvinceName = x.ProvinceName,
                City = x.City,
                ExecutionSite = x.ExecutionSite,
                OverallExpenses = x.OverallExpenses,
                OrderStatus = string.IsNullOrEmpty(bu.ProcessStatusName) ? "未知" : bu.ProcessStatusName,
                CreateTime = x.AddTime.ToString("yyyy-MM-dd HH:mm:ss"),
                UserName = x.AddUserName,
                Applicant = x.Applicant,
                CostCenter = x.CostCenter,
                SaleDepartment = x.SaleDepartment,
                MeetTime = x.MeetTime != null ? x.MeetTime.Value.ToString("yyyy-MM-dd") : "",
                MeetPlace = x.MeetPlace,
                MeetNumber = x.MeetNumber,
                IsNeedRentMeet = x.IsNeedRentMeet,
                MeetBudget = x.MeetBudget.Value,
                MeetType = x.MeetType,
                CustomerCode = x.CustomerCode,
                CustomerName = x.CustomerName,
                DesignCode = x.DesignCode,
                DesignName = x.DesignName,
                Guild = x.Guild,
                Division = x.Division,
                MeetModality = x.MeetModality,
                MeetModalityOther = x.MeetModalityOther,
                ExpectExtras = x.ExpectExtras,
                BusinessNumber = x.BusinessNumber,
                EntryName = x.EntryName,
                ProjectProperties = x.ProjectProperties,
                BigCustomerType = x.BigCustomerType,
                BigCustomerCode = x.BigCustomerCode,
                BigCustomerName = x.BigCustomerName,
                Projectlocation = x.Projectlocation,
                ProjectCity = x.ProjectCity,
                ProjectStatus = x.ProjectStatus,
                BusinessSource = x.BusinessSource,
                CustomerType1 = x.CustomerType1,
                CustomerType2 = x.CustomerType2,
                SingnTime = x.SingnTime != null ? x.SingnTime.Value.ToString("yyyy-MM-dd") : "",
                ProjectAssurance = x.ProjectAssurance,
                CoatingArea = x.CoatingArea,
                ShipmentAmount = x.ShipmentAmount,
                ActiveTime = x.ActiveTime != null ? x.ActiveTime.Value.ToString("yyyy-MM-dd") : "",
                PlanJoinCustomer = x.PlanJoinCustomer,
                RealityJoinCustomer = x.RealityJoinCustomer,
                RealityJoinPerson = x.RealityJoinPerson,
                ActivityEvaluation = x.ActivityEvaluation,
                Feedback = x.Feedback,
                CaseDate = x.CaseDate != null ? x.CaseDate.Value.ToString("yyyy-MM-dd HH:mm:ss") : ""
            }).OrderBy(x => x.AddTime, OrderByType.Desc); 

            return listQueryable;
        }
    }
}
